/**
 * Copyright (C) 2019 Baidu, Inc. All Rights Reserved.
 */
package com.bailiban.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bailiban.dao.TicketsDao;
import com.bailiban.pojo.Stadia;
import com.bailiban.pojo.Tickets;
import com.bailiban.utils.JDBCUtils;
import com.bailiban.utils.ResultMsg;

/**
 * @author zhulang
 * @version 创建时间：2019年9月27日 上午11:46:58
 */
public class TicketsDaoImpl implements TicketsDao {
    private static Connection connection = null;
    private static PreparedStatement statement = null;
    private static ResultSet rs = null;

    /*
     * (non-Javadoc)
     * 
     * @see com.bailiban.dao.TicketsDao#findAllTickets()
     */
    @Override
    public List<Tickets> findAllTickets() {
        List<Tickets> list = new ArrayList<Tickets>();
        try {
            connection = JDBCUtils.getConnection();
            String sql =
                    "SELECT t.tid,t.TNAME,t.beginTime,t.totalCount,t.orderCount,t.price,t.description,t.s_t_id,s.sid,s.sname,s.address,s.description from tb_tickets t,tb_stadia s WHERE t.s_t_id=s.sid";
            statement = connection.prepareStatement(sql);
            rs = statement.executeQuery();
            while (rs.next()) {
                Tickets tickets = new Tickets();
                tickets.setTid(rs.getInt("tid"));
                tickets.setTname(rs.getString("tname"));
                tickets.setBeginTime(rs.getDate("beginTime"));
                tickets.setDescription(rs.getString("description"));
                tickets.setOrderCount(rs.getInt("orderCount"));
                tickets.setPrice(rs.getDouble("price"));
                tickets.setS_t_id(rs.getInt("s_t_id"));
                tickets.setTotalCount(rs.getInt("totalCount"));
                Stadia stadia = new Stadia();
                stadia.setSid(rs.getInt("sid"));
                stadia.setSname(rs.getString("sname"));
                stadia.setAddress(rs.getString("address"));
                stadia.setDescription(rs.getString("description"));
                tickets.setStadia(stadia);
                list.add(tickets);
            }
            JDBCUtils.close(connection, statement, rs);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return list;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.bailiban.dao.TicketsDao#findTicketsByTid(int)
     */
    @Override
    public Tickets findTicketsByTid(int tid) {
        Tickets tickets = new Tickets();
        try {
            connection = JDBCUtils.getConnection();
            String sql =
                    "SELECT t.tid,t.TNAME,t.beginTime,t.totalCount,t.orderCount,t.price,t.description,t.s_t_id,s.sid,s.sname,s.address,s.description from tb_tickets t,tb_stadia s WHERE t.s_t_id=s.sid and t.tid=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, tid);
            rs = statement.executeQuery();
            while (rs.next()) {
                tickets.setTid(rs.getInt("tid"));
                tickets.setTname(rs.getString("tname"));
                tickets.setBeginTime(rs.getDate("beginTime"));
                tickets.setDescription(rs.getString("description"));
                tickets.setOrderCount(rs.getInt("orderCount"));
                tickets.setPrice(rs.getDouble("price"));
                tickets.setS_t_id(rs.getInt("s_t_id"));
                tickets.setTotalCount(rs.getInt("totalCount"));
                Stadia stadia = new Stadia();
                stadia.setSid(rs.getInt("sid"));
                stadia.setSname(rs.getString("sname"));
                stadia.setAddress(rs.getString("address"));
                stadia.setDescription(rs.getString("description"));
                tickets.setStadia(stadia);
            }
            JDBCUtils.close(connection, statement, rs);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return tickets;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.bailiban.dao.TicketsDao#addTickets(com.bailiban.pojo.Tickets)
     */
    @Override
    public ResultMsg addTickets(Tickets tickets) {
        ResultMsg result = new ResultMsg();
        try {
            connection = JDBCUtils.getConnection();
            String sql =
                    "INSERT INTO tb_tickets(TNAME,beginTime,totalCount,orderCount,price,description,s_t_id) VALUES(?,?,?,?,?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1, tickets.getTname());
            statement.setDate(2, new Date(tickets.getBeginTime().getTime()));
            statement.setInt(3, tickets.getTotalCount());
            statement.setInt(4, tickets.getOrderCount());
            statement.setDouble(5, tickets.getPrice());
            statement.setString(6, tickets.getDescription());
            statement.setInt(7, tickets.getS_t_id());
            int update = statement.executeUpdate();
            if (update > 0) {
                result.setFlag(true);
            } else {
                result.setFlag(false);
            }
            JDBCUtils.close(connection, statement, rs);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return result;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.bailiban.dao.TicketsDao#updateTickets(com.bailiban.pojo.Tickets)
     */
    @Override
    public ResultMsg updateTickets(Tickets tickets) {
        ResultMsg result = new ResultMsg();
        try {
            connection = JDBCUtils.getConnection();
            String sql =
                    "UPDATE tb_tickets SET TNAME=?,beginTime=?,totalCount=?,orderCount=?,price=?,description=?,s_t_id=? WHERE tid=?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, tickets.getTname());
            statement.setDate(2, new Date(tickets.getBeginTime().getTime()));
            statement.setInt(3, tickets.getTotalCount());
            statement.setInt(4, tickets.getOrderCount());
            statement.setDouble(5, tickets.getPrice());
            statement.setString(6, tickets.getDescription());
            statement.setInt(7, tickets.getS_t_id());
            statement.setInt(8, tickets.getTid());
            int update = statement.executeUpdate();
            if (update > 0) {
                result.setFlag(true);
            } else {
                result.setFlag(false);
            }
            JDBCUtils.close(connection, statement, rs);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return result;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.bailiban.dao.TicketsDao#deleteTicketsByTid(int)
     */
    @Override
    public ResultMsg deleteTicketsByTids(List<Integer> tids) {
        ResultMsg result = new ResultMsg();
        try {
            connection = JDBCUtils.getConnection();

            String sql = "DELETE from tb_tickets WHERE 1=1  ";
            StringBuilder builder = new StringBuilder();
            builder.append(sql);
            // 拼接sql条件
            for (int i = 0; i < tids.size(); i++) {
                if (i == 0) {
                    builder.append(" and tid=? ");
                } else {
                    builder.append(" or tid=? ");
                }

            }
            System.out.println("--------------");
            sql = builder.toString();
            System.out.println(sql);
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < tids.size(); i++) {
                statement.setInt(i + 1, tids.get(i));
            }

            int update = statement.executeUpdate();
            if (update > 0) {
                result.setFlag(true);
            } else {
                result.setFlag(false);
            }
            JDBCUtils.close(connection, statement, rs);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return result;
    }

}
